"""
Case Type   : 空串和null
Case Name   : 兼容pg库下设置参数并验证null和空字符串
Create At   : 2023.08.07
Owner       : opentestcase005
Description :
    1、创建兼容b数据库
    2、连接pg库查询参数默认值
    3、pg库下建表并插入空字符串
    4、查询
    5、pg库重新建表并插入空串
    6、设置参数并查询
    7、设置参数为空并查询
    8、查询
    9、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功，c列类型为固定字符，故空串要补足10个，长度为10
    5、成功
    6、成功，(非a库可以设置，但不生效)，和默认行为保持一致，空串和null不同
    7、成功，(非a库可以设置，但不生效)，和默认行为保持一致，空串和null不同
    8、成功，不设置pad_char_to_full_length参数值，查询删除char类型尾部空格，
    空串和null值不同
    9、成功
History     :
    modified by l1azzzy: behavior_compat_options默认值已修改。
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class CompatibilityTest53(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = "db_guc_0053"
        self.tb_name = "tb_guc0053"

    def test_atan(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        text = '-----step1:创建兼容pg数据库 expect:成功-----'
        self.log.info(text)
        create_cmd = f"drop database if exists {self.db_name};" \
                     f"create database {self.db_name} dbcompatibility 'pg';"
        msg = self.sh_primary.execut_db_sql(create_cmd)
        self.log.info(msg)
        self.assertIn('CREATE DATABASE', msg, '执行失败:' + text)

        text = '-----step2:连接pg库查询参数默认值 expect:成功，默认值为空-----'
        self.log.info(text)
        sql_cmd = "show behavior_compat_options;"
        msg = self.sh_primary.execut_db_sql(sql_cmd,
                                            dbname=self.db_name)
        self.log.info(msg)
        self.assertEqual('sql_mode_full_group',  msg.splitlines()[2].strip(), '执行失败:' + text)

        text = '-----step3:pg库下建表并插入空字符串 expect:成功-----'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name};" \
                  f"create table {self.tb_name}(c char(10));" \
                  f"insert into {self.tb_name} values('');"
        msg = self.sh_primary.execut_db_sql(sql_cmd,
                                            dbname=self.db_name)
        self.log.info(msg)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS,
                      msg, '执行失败:' + text)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG,
                      msg, '执行失败:' + text)

        text = '--step4:查询 expect:插入一个只包含空格的字符串，pg会将其填充到指定长度，' \
               '但是由于字符串中只包含空格，因此长度为0的字符并不会被计算在内-'
        self.log.info(text)
        sql_cmd = f"select length(c) from {self.tb_name} where c = '';" \
                  f"select length(c) from {self.tb_name} where c = ' ';" \
                  f"select * from {self.tb_name} where c is null;"
        msg = self.sh_primary.execut_db_sql(sql_cmd,
                                            dbname=self.db_name)
        self.log.info(msg)
        self.assertEqual('0', msg.splitlines()[2].strip(), '执行失败:' + text)
        self.assertEqual('0', msg.splitlines()[7].strip(), '执行失败:' + text)
        self.assertEqual('(0 rows)', msg.splitlines()[-1].strip(),
                         '执行失败:' + text)

        text = '-step5:pg库重新建表并插入空串;expect:成功-'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name}_01;" \
                  f"create table {self.tb_name}_01(c char(10));" \
                  f"insert into {self.tb_name}_01 values('');"
        msg = self.sh_primary.execut_db_sql(sql_cmd,
                                            dbname=self.db_name)
        self.log.info(msg)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS,
                      msg, '执行失败:' + text)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG,
                      msg, '执行失败:' + text)

        text = '--step6:设置新增参数值并查询 expect:成功,和默认行为保持一致-'
        self.log.info(text)
        sql_cmd = f"set behavior_compat_options='accept_empty_str';" \
                  f"select length(c) from {self.tb_name} where c = '';" \
                  f"select length(c) from {self.tb_name} where c = ' ';" \
                  f"select * from {self.tb_name} where c is null;"
        msg = self.sh_primary.execut_db_sql(sql_cmd,
                                            dbname=self.db_name)
        self.log.info(msg)
        self.assertIn('SET', msg, '执行失败:' + text)
        self.assertEqual('0', msg.splitlines()[3].strip(), '执行失败:' + text)
        self.assertEqual('0', msg.splitlines()[8].strip(), '执行失败:' + text)
        self.assertEqual('(0 rows)', msg.splitlines()[-1].strip(),
                         '执行失败:' + text)

        text = '--step7:设置参数为空并查询 expect:成功，(非a库可以设置，但不生效)，' \
               '和默认行为保持一致，空串和null不同-'
        self.log.info(text)
        sql_cmd = f"set behavior_compat_options='';" \
                  f"select length(c) from {self.tb_name} where c = '';" \
                  f"select length(c) from {self.tb_name} where c = ' ';" \
                  f"select * from {self.tb_name} where c is null;"
        msg = self.sh_primary.execut_db_sql(sql_cmd,
                                            dbname=self.db_name)
        self.log.info(msg)
        self.assertEqual('0', msg.splitlines()[3].strip(), '执行失败:' + text)
        self.assertEqual('0', msg.splitlines()[8].strip(), '执行失败:' + text)
        self.assertEqual('(0 rows)', msg.splitlines()[-1].strip(),
                         '执行失败:' + text)

    def tearDown(self):
        text = '----step8:清理环境;expect:成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};"
        msg = self.sh_primary.execut_db_sql(sql_cmd, dbname='postgres')
        self.log.info(msg)
        self.assertIn('DROP DATABASE', msg, '执行失败:' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
